using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Material.Object;
using Material.DAL;
namespace Material.Entity
{
	public class TblSatuanEntity
	{
		private Accessor accessor;
		private string connString;

		public TblSatuanEntity (string _connString)
		{
			 this.connString = _connString;
		}

		public int Insert(TblSatuan tblsatuan)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" INSERT INTO [TblSatuan]");
            sql.AppendLine(" (");
            sql.AppendLine(" [Satuan]");
            sql.AppendLine(" ,[Keterangan]");
            sql.AppendLine(" ,[CreatedDate]");
            sql.AppendLine(" ,[CreatedBy]");
            sql.AppendLine(" )");
            sql.AppendLine(" VALUES");
            sql.AppendLine(" (");
            sql.AppendLine(" @Satuan");
            sql.AppendLine(" ,@Keterangan");
            sql.AppendLine(" ,@CreatedDate");
            sql.AppendLine(" ,@CreatedBy");
            sql.AppendLine(" )");

			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add("@Satuan" ,tblsatuan.Satuan );
			 accessor.Add("@Keterangan" ,tblsatuan.Keterangan );
			 accessor.Add("@CreatedDate" ,tblsatuan.CreatedDate );
			 accessor.Add("@CreatedBy" ,tblsatuan.CreatedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Update(TblSatuan tblsatuan)
		{

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" UPDATE [TblSatuan]");
            sql.AppendLine(" SET [Satuan] = @Satuan");
            sql.AppendLine(" ,[Keterangan] =@Keterangan");
            sql.AppendLine(" ,[ModifiedDate] = @ModifiedDate");
            sql.AppendLine(" ,[ModifiedBy] = @ModifiedBy");
            sql.AppendLine(" WHERE Id=@Id");


			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add(@"Id" ,tblsatuan.Id );
			 accessor.Add(@"Satuan" ,tblsatuan.Satuan );
			 accessor.Add(@"Keterangan" ,tblsatuan.Keterangan );
			 accessor.Add(@"ModifiedDate" ,tblsatuan.ModifiedDate );
			 accessor.Add(@"ModifiedBy" ,tblsatuan.ModifiedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Delete(TblSatuan tblsatuan)
		{

             StringBuilder sql = new StringBuilder();
             sql.AppendLine(" DELETE FROM [TblSatuan]");
             sql.AppendLine(" WHERE Id= @Id");

			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add(@"Id" ,tblsatuan.Id);

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public TblSatuan GetDataByID(TblSatuan tblsatuan)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[Satuan]");
            sql.AppendLine(",[Keterangan]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblSatuan]");
            sql.AppendLine(" WHERE Id=@Id");

			 TblSatuan result = new TblSatuan();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add(@"Id" ,tblsatuan.Id);

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 result.Id = rdr.GetInt32(0);
						 result.Satuan = rdr.GetString(1);
						 result.Keterangan = rdr.GetString(2);
						 result.CreatedDate = rdr.GetDateTime(3);
						 result.CreatedBy = rdr.GetString(4);
                         if (rdr[5] != DBNull.Value)
						 result.ModifiedDate = rdr.GetDateTime(5);
                         if (rdr[6] != DBNull.Value)
						 result.ModifiedBy = rdr.GetString(6);
					  }
			}
			 return result;
		}

		public List<TblSatuan> GetDataAll()
		{

            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[Satuan]");
            sql.AppendLine(",[Keterangan]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblSatuan]");
            sql.AppendLine(" ORDER BY SATUAN ASC");


			 List<TblSatuan> result = new List<TblSatuan>();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblSatuan_SelectAll";
			 accessor.CommandType = CommandType.StoredProcedure;

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 TblSatuan obj = new TblSatuan();
						 obj.Id = rdr.GetInt32(0);
						 obj.Satuan = rdr.GetString(1);
						 obj.Keterangan = rdr.GetString(2);
						 obj.CreatedDate = rdr.GetDateTime(3);
						 obj.CreatedBy = rdr.GetString(4);
                         if (rdr[5] != DBNull.Value)
						    obj.ModifiedDate = rdr.GetDateTime(5);
                        if (rdr[6] != DBNull.Value)
						 obj.ModifiedBy = rdr.GetString(6);
						 result.Add(obj);
					  }
			}
			 return result;
		}

        public DataSet GetFindData(TblSatuan tblSatuan)
        {
            DataSet ds = new DataSet();
            
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[Satuan]");
            sql.AppendLine(",[Keterangan]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblSatuan]");
            sql.AppendLine("WHERE Satuan LIKE '%" + tblSatuan.Satuan + "%'");
            sql.AppendLine("ORDER BY SATUAN ASC");

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;

            ds = Excutor.GetDataSet(accessor);

            return ds;

        }


        public SqlCommand GetCommandReport()
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[Satuan]");
            sql.AppendLine(",[Keterangan]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine(" FROM [TblSatuan]");
            sql.AppendLine(" ORDER BY SATUAN ASC");


            SqlCommand comm = new SqlCommand();
           
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }

	}
}
